package com.clive.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import java.util.List;

import org.apache.commons.dbutils.DbUtils;

import com.clive.bean.Dishes;
import com.clive.bean.DishesCategory;
import com.clive.dao.DishesDao;
import com.clive.utils.DbcpUtils;

public class DishesDaoImpl implements DishesDao{

	public int addDishes(Dishes dishes) {
		Connection conn = DbcpUtils.getConnection();
		String sql = "INSERT INTO dishes(dish_name,price,buy_count,pic_path,created,updated,category_id,status) VALUE(?,?,?,?,?,?,?,?)";
		PreparedStatement pst = null;
		try {
			pst = conn.prepareStatement(sql);
			pst.setString(1, dishes.getDishName());
			pst.setDouble(2, dishes.getPrice());
			pst.setInt(3, dishes.getBuyCount());
			pst.setString(4, dishes.getPicPath());
			pst.setDate(5, new Date(dishes.getCreated().getTime()));
			pst.setDate(6, new Date(dishes.getUpdated().getTime()));
			pst.setInt(7, dishes.getCategory().getId());
			pst.setInt(8, dishes.getStatus());
			int i = pst.executeUpdate();
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbcpUtils.closeAll(conn, pst);
		}
		return 0;
	}


	public List<Dishes> findDishesByCurrentPage(int index, Integer limit) {
		Connection conn = DbcpUtils.getConnection();
		PreparedStatement pst = null;
		ResultSet result = null;
		String sql = "SELECT a.id,a.dish_name,a.price,a.buy_count,a.pic_path,b.dishes_category_name,b.id category_id,a.created,a.updated FROM dishes a INNER JOIN dishescategory b ON a.category_id = b.id WHERE a.status=1 LIMIT ?,?";
		try {
			pst = conn.prepareStatement(sql );			
			//index当前索引
			pst.setInt(1, index);
			pst.setInt(2, limit);
			result = pst.executeQuery();
			List<Dishes> list = new ArrayList<Dishes>();
			while (result.next()) {
				Dishes dishes = new Dishes();
				dishes.setId(result.getInt("id"));
				dishes.setDishName(result.getString("dish_name"));
				dishes.setPrice(result.getDouble("price"));
				dishes.setBuyCount(result.getInt("buy_count"));
				dishes.setPicPath(result.getString("pic_path"));
				dishes.setCreated(result.getDate("created"));
				dishes.setUpdated(result.getDate("updated"));
				DishesCategory category = new DishesCategory();
				category.setId(result.getInt("category_id"));
				category.setDishesCategoryName(result.getString("dishes_category_name"));
				dishes.setCategory(category);
				list.add(dishes);		
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbcpUtils.closeAll(conn, pst, result);
		}
		
		return null;
	}

	public int findDishesByCount() {
		Connection conn = DbcpUtils.getConnection();
		PreparedStatement pst = null;
		ResultSet result = null;
		String sql = "SELECT COUNT(*) FROM dishes a INNER JOIN dishescategory b ON a.category_id = b.id WHERE a.status=1";
		try {
			pst = conn.prepareStatement(sql );
			result = pst.executeQuery();
			if (result.next()) {
				int count = result.getInt("COUNT(*)");
				return count;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbcpUtils.closeAll(conn, pst, result);
		}
		
		return 0;
	}

	public int updateDishes(Dishes dishes) {
		Connection conn = DbcpUtils.getConnection();
		PreparedStatement pst = null;
		String sql = "UPDATE dishes SET dish_name = ? ,price = ?, category_id = ?,updated = ? WHERE id = ?";
		try {
			pst = conn.prepareStatement(sql );
			pst.setString(1, dishes.getDishName());
			pst.setDouble(2, dishes.getPrice());
			pst.setInt(3, dishes.getCategory().getId());
			pst.setDate(4, new Date(dishes.getUpdated().getTime()));
			pst.setInt(5, dishes.getId());
			int i = pst.executeUpdate();
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbcpUtils.closeAll(conn, pst);
		}
		
		return 0;
	}

	public int deleteDishesById(Integer dishesId) {
		Connection conn = DbcpUtils.getConnection();
		PreparedStatement pst = null;
		String sql = "UPDATE dishes SET status = 0 WHERE id = ?";
		try {
			pst = conn.prepareStatement(sql );
			pst.setInt(1, dishesId);
			int i = pst.executeUpdate();
			return i;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbcpUtils.closeAll(conn, pst);
		}
		
		return 0;
	}

	public List<Dishes> findDishesByName(String dishName) {
		// TODO Auto-generated method stub
		return null;
	}

	public List<Dishes> findDishesByCategoryId(Integer dishesCategoryId) {
		// TODO Auto-generated method stub
		return null;
	}


	@Override
	public Dishes findDishesById(Integer dishId) {
		Connection conn = DbcpUtils.getConnection();
		PreparedStatement pst = null;
		ResultSet result = null;
		String sql = "SELECT a.id,a.dish_name,a.price,a.pic_path,b.id category_id,b.dishes_category_name  FROM dishes a LEFT JOIN dishescategory b ON a.category_id=b.id WHERE a.id = ? AND a.status = 1";
		try {
			pst = conn.prepareStatement(sql);
			pst.setInt(1, dishId);
			//executeQuery是查询方法，在数据库里使用SELECT指令
			result = pst.executeQuery();
			
			if (result.next()) {//这里用while循环也可以
				Dishes dishes = new Dishes();
				dishes.setId(result.getInt("id"));
				dishes.setPrice(result.getDouble("price"));
				dishes.setDishName(result.getString("dish_name"));
				dishes.setPicPath(result.getString("pic_path"));
				DishesCategory category = new DishesCategory();
				category.setId(result.getInt("category_id"));
				category.setDishesCategoryName(result.getString("dishes_category_name"));
				dishes.setCategory(category);
				return dishes;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbcpUtils.closeAll(conn, pst, result);
		}
		
		return null;
	}

}
